Why use MySQL?
There are many great DBMSs out there, including MySQL, PostgreSQL, SQLite, Oracle, and SQL Server, and all of them can be used for most web development purposes. That said, MySQL does have a few advantages for web developers compared to some other systems: It's open source, which means it's free for anyone to use and modify. It's widely available. MySQL can be installed on many different platforms, and it usually comes standard with most web hosting setups. It's easy to use. Setting up and working with MySQL databases is relatively straightforward. It works well with PHP. As of version 5.3, PHP has a native MySQL driver that is tightly coupled with the PHP engine, making it a good choice for PHP coders.Each DBMS has its own strengths and weaknesses. For example, PostgreSQL is also open source, is very stable, and has a great community behind it. SQLite is extremely fast and self-contained (and is also free), while Oracle and SQL Server have a lot of enterprise-level features that make it a good choice for large organizations.
Installing MySQL
As I mentioned above, most web hosting accounts come with MySQL pre-installed. However, if you're developing websites using MySQL, you also want to have the MySQL server running on your own computer, so that you can create and test your databases and code without needing to upload files to your live server all the time. There are two main components to MySQL:
mysqld. This is the MySQL DBMS that does the actual work of managing your databases. It runs all the time in the background, accepting connections from client programs, web scripts and so on.
Various client and utility programs. These include mysql, the command-line MySQL Monitor client that you'll use later in the tutorial to issue commands to the server. You'll also find programs like mysqladmin for administering MySQL databases, and mysqldump for exporting and backing up databases.
In addition, many MySQL installs include documentation, header files for developers, and the MySQL test suite. There are many ways to install the MySQL server and associated programs. Here are three ways you can do it:
Other popular packages similar to XAMPP include WampServer and EasyPHP for Windows, and MAMP for Mac OS X.
mysql, the MySQL Monitor. This is a command-line client program that you can use to connect to the server and issue commands.
So let's try firing up the MySQL Monitor and see what it can do. Follow these two steps:
mysql program in the terminal window:The -u root parameter tells the MySQL Monitor to connect to the MySQL server using the root user, which is always available with MySQL. By default, MySQL's root user doesn't need a password. This is OK for a development setup on your computer, but a terrible idea for a live server! If you're installing MySQL on a live server, make sure you secure it properly. XAMPP also comes with some security scripts that can automatically make your XAMPP installation more secure.
Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3893 Server version: 5.5.8 Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> _The last line,
mysql>, is the MySQL prompt. This is where you type your commands to send to the MySQL server.
Let's try out a couple of commands. Type the following at the mysql> prompt, then press Enter:
select now();This tells MySQL to get the current date and time and display it. You'll see something like this appear:
+---------------------+ | now() | +---------------------+ | 2011-08-24 11:36:40 | +---------------------+ 1 row in set (0.00 sec)Now try another command:
show databases;This command lists all the MySQL databases on your computer. Since you've just installed MySQL, there will just be a few default databases, similar to the following:
+--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.00 sec)Now that you know how to send commands to your MySQL server, you're ready to create your own database and start adding data to it. You'll do this in the following sections.
When you're finished with the MySQL Monitor, you can quit it by typing exit at the prompt and pressing Enter.
If you're not comfortable with the command line, there are other ways to administer MySQL and issue commands. MySQL Workbench is a free graphical app that can connect to any MySQL server and administer it. There's also the web-based phpMyAdmin, which is included in many LAMP/WAMP/MAMP packages.
mysql> prompt, type the following and press Enter:
create database bookstore;If all goes well, you'll see something like this:
Query OK, 1 row affected (0.05 sec)MySQL has now created your database. You can check this by typing show databases again:
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | bookstore | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.00 sec)Congratulations — you've just created your first MySQL database!
select now(), show databases, and create database bookstore — are SQL statements. SQL, or Structured Query Language, is the language you use to communicate with most DBMSs, including MySQL. Using SQL, you can create and delete databases and tables; insert new data into tables; update data; delete data; and retrieve data.
Statements that retrieve data from a database are also commonly called queries, hence the name "Structured Query Language".
You'll use SQL in the rest of this tutorial as you create a table in your new database, add a record, and retrieve a record.
books table, you might have one field for the book title, another field for the book's author, and so on.
One or more records. A record is a set of field values that stores all the information about a particular entity in the table. In a books table, a record would store all the field values for a specific book.
books table to hold books in our book store:
id |
title |
author |
price |
|---|---|---|---|
1 |
The Grapes of Wrath |
John Steinbeck |
12.99 |
2 |
Nineteen Eighty-Four |
George Orwell |
8.99 |
3 |
The Wind-Up Bird Chronicle |
Haruki Murakami |
7.99 |
id, title, author, and price. The next three rows are the three book records in our table. Each record has its own field values: for example, the first record's title field contains "The Grapes of Wrath", while the second record's title field contains "Nineteen Eighty-Four".
Since a single column in a table holds all the different record values for a specific field, fields are also commonly known as columns. Similarly, the records in a table are commonly called rows.
So how do you actually create this table in MySQL? To do this, you need to create a schema for the table. This is a text file containing a series of SQL statements that create the table and define the table's fields.
Here's the schema — save it as a file called books.sql somewhere on your computer:
USE bookstore;
DROP TABLE IF EXISTS books;
CREATE TABLE books
(
id int unsigned NOT NULL auto_increment, # Unique ID for the record
title varchar(255) NOT NULL, # Full title of the book
author varchar(255) NOT NULL, # The author of the book
price decimal(10,2) NOT NULL, # The price of the book
PRIMARY KEY (id)
);
USE bookstorebookstore database that you created earlier. MySQL will then carry out all further operations on this database.
DROP TABLE IF EXISTS booksbooks table from the database, since you can't redefine a table if it already exists.
Be careful when using DROP TABLE. When you delete a table like this, any data in the table is gone forever!
CREATE TABLE books ( ... )
This statement creates a new table called books. The stuff in between the parentheses defines the table's fields and its primary key, as we'll see next.
id int unsigned NOT NULL auto_increment
The first field we define is id. This is a special type of field that assigns a unique numeric ID to each book record in the table. Most of the time, you'll want your table to have a unique field of some sort, so that you can easily identify a particular record. We give the field an int unsigned type, which can hold large, positive integer numbers. We also add the auto_increment attribute to the field — now, whenever we add a new record to the table, MySQL will automatically assign a new, unique value to the record's id field (starting with 1).
The NOT NULL constraint prevents the field containing NULL values. In MySQL, NULL is a special type of value that can be useful in some situations. However, it can also be quite confusing for beginners, so we won't use them in this tutorial.
title varchar(255) NOT NULL
Next we define the field to hold each book's title. We give it a varchar(255) type, which means it can hold a text string up to 255 characters long.
author varchar(255) NOT NULL
The next field is the book's author. As with the title field, we give it the varchar(255) type.
price decimal(10,2) NOT NULL
The last field is the book's price. We give this field a decimal(10,2) type, which means that the field can hold a 10-digit decimal number, with 2 of the digits sitting to the right of the decimal point.
PRIMARY KEY (id)
Finally, we create a primary key based on the table's id field. A primary key uniquely identifies records in the table; a table can have only one primary key. MySQL also creates an index using the primary key — this lets you retrieve a book record extremely quickly by referencing its id field, even if the table contains millions of rows.
mysql> prompt:
source /path/to/books.sql...where
/path/to/books.sql is the full path to your books.sql file. (If you ran mysql in the same folder as your books.sql file then you can just type source books.sql.)
You should see the following output in MySQL Monitor:
Database changed Query OK, 0 rows affected, 1 warning (0.00 sec) Query OK, 0 rows affected (0.10 sec)To check that your
books table was created, you can type show tables:
mysql> show tables; +---------------------+ | Tables_in_bookstore | +---------------------+ | books | +---------------------+ 1 row in set (0.00 sec)You can even inspect the table schema to make sure it's correct. To do this, use the
explain command, like this:
mysql> explain books; +--------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | title | varchar(255) | NO | | NULL | | | author | varchar(255) | NO | | NULL | | | price | decimal(10,2) | NO | | NULL | | +--------+------------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec)
bookstore, and added a books table to it. Let's try adding a record to the books table.
To add a record to a table, you use the SQL INSERT statement, passing in the record's field names and values. Type the following line in the MySQL Monitor to insert a book record into your table:
INSERT INTO books ( title, author, price ) VALUES ( "The Grapes of Wrath", "John Steinbeck", 12.99 );You should see the following output, indicating that MySQL has added the row to the table:
Query OK, 1 row affected (0.06 sec)As you can see, we've used an
INSERT statement to add the book "The Grapes of Wrath" to the table. We specified INTO books to tell MySQL which table to insert the record into, then listed the field names that we want to supply values for in parentheses, followed by the keyword VALUES, followed by the field values in the same order as the field names, again in parentheses.
Notice that we haven't specified a value for the id field. Since it's an auto_increment field, MySQL generates the field value automatically.
Let's add another couple of books to the table:
mysql> INSERT INTO books ( title, author, price )
VALUES ( "Nineteen Eighty-Four", "George Orwell", 8.99 ),
( "The Wind-Up Bird Chronicle", "Haruki Murakami", 7.99 );
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
As you can see, you can insert multiple rows at once by supplying multiple sets of field values, enclosed in parentheses and separated by commas.
SELECT statement lets you retrieve one or more records from a table — or even multiple tables at once — based on criteria that you supply. The basic syntax is:
SELECT fieldNames FROM tableName [WHERE criteria]
There's a lot more to the SELECT statement than this, but we'll keep things simple in this tutorial!
Let's try a basic SELECT query on our books table using the MySQL Monitor:
mysql> SELECT * FROM books; +----+----------------------------+-----------------+-------+ | id | title | author | price | +----+----------------------------+-----------------+-------+ | 1 | The Grapes of Wrath | John Steinbeck | 12.99 | | 2 | Nineteen Eighty-Four | George Orwell | 8.99 | | 3 | The Wind-Up Bird Chronicle | Haruki Murakami | 7.99 | +----+----------------------------+-----------------+-------+ 3 rows in set (0.00 sec)This
SELECT query retrieves all fields (*) from the books table. Since we haven't supplied any additional criteria, the query retrieves all the records in the table, and displays the field values in the MySQL monitor.
As you can see, MySQL has auto-generated the values for the id field, beginning with 1.
What if we want to retrieve just one record from the table, such as the book "Nineteen Eighty-Four"? To narrow down the selection, we can add a WHERE clause, like this:
mysql> SELECT * FROM books WHERE id = 2; +----+----------------------+---------------+-------+ | id | title | author | price | +----+----------------------+---------------+-------+ | 2 | Nineteen Eighty-Four | George Orwell | 8.99 | +----+----------------------+---------------+-------+ 1 row in set (0.00 sec)As well as selecting by the
id field, we can select by any other field we like:
mysql> SELECT * FROM books WHERE title = "Nineteen Eighty-Four"; +----+----------------------+---------------+-------+ | id | title | author | price | +----+----------------------+---------------+-------+ | 2 | Nineteen Eighty-Four | George Orwell | 8.99 | +----+----------------------+---------------+-------+ 1 row in set (0.01 sec)We can also use other operators, such as
< (less than), > (greater than), and the boolean AND operator, to retrieve a range of records:
mysql> SELECT * FROM books WHERE price < 10 AND price > 5; +----+----------------------------+-----------------+-------+ | id | title | author | price | +----+----------------------------+-----------------+-------+ | 2 | Nineteen Eighty-Four | George Orwell | 8.99 | | 3 | The Wind-Up Bird Chronicle | Haruki Murakami | 7.99 | +----+----------------------------+-----------------+-------+ 2 rows in set (0.00 sec)Finally, instead of retrieving all fields using
*, we can specify just the field or fields we want to retrieve. Here's an example:
mysql> SELECT title, author FROM books; +----------------------------+-----------------+ | title | author | +----------------------------+-----------------+ | The Grapes of Wrath | John Steinbeck | | Nineteen Eighty-Four | George Orwell | | The Wind-Up Bird Chronicle | Haruki Murakami | +----------------------------+-----------------+ 3 rows in set (0.00 sec)As you can see,
SELECT queries make it easy to retrieve just the records and fields you want from your table.
mysql, to connect to your MySQL server and issue commands.
How to create databases with the CREATE DATABASE command.
How to create tables with the CREATE TABLE command.
A few MySQL data types, including int, varchar, and decimal.
The concepts of NULL values, auto-increment fields, unique fields, and primary keys.
Adding records to a table using the INSERT statement, and
Retrieving records from a table with the SELECT statement.